package com.promotores;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONException;



import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class GestorEvento extends CreaTablas {
	CreaTablas db;
	Context context;
	// TipoEvento table name
	private static final String TABLE_EVENTO = "Evento";

	// TipoEvento Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NOMBRE = "Nombre";
	private static final String KEY_DESC = "Descripcion";
	private static final String KEY_ID_TIPO_EVENTO = "idTipoEvento";
	private static final String KEY_FECHAINI = "FechaIni";
	private static final String KEY_FECHAFIN = "FechaFin";
	private static final String KEY_ESTADO = "Estado";
	private static final String KEY_URL_IMAGE = "Url";
	private static final String KEY_PROMOTOR = "nomPromotor";
	private static final String KEY_APPATERNO = "apePaterno";
	private static final String KEY_APMATERNO = "apeMaterno";
	private static final String KEY_EMAIL = "email";
	private static final String KEY_FLAG_DELETED = "deleted";
	private static final String KEY_FLAG_SAVED = "flagSaved";
    private static final String KEY_CREATED_AT = "created_at";
	private static final String KEY_UPDATED_AT = "updated_at";
	private static final String KEY_AMBIENTE = "Ambiente";
	private static final String KEY_ID_SUBTIPO_EVENTO = "idSubTipoEvento";
	
	public GestorEvento(Context context) {
		super(context);
		this.context = context;
		db = new CreaTablas(context);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 */

	// Adding new tipoEvento
	public void addEvento(BeanEvento evento, String flag_syn) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_ID, evento.getId());
		values.put(KEY_NOMBRE, evento.getNombre());
		values.put(KEY_DESC, evento.getDescripcion());
		values.put(KEY_ID_TIPO_EVENTO, evento.getTipoEvento());
		values.put(KEY_FECHAINI, evento.getFechaIni());
		values.put(KEY_FECHAFIN, evento.getFechaFin());
		values.put(KEY_ESTADO, evento.getEstado());
		values.put(KEY_URL_IMAGE, evento.getUrlImagen());
		values.put(KEY_PROMOTOR, evento.getNomPromotor());
		values.put(KEY_APPATERNO, evento.getApePaterno());
		values.put(KEY_APMATERNO, evento.getApeMaterno());
		values.put(KEY_EMAIL, evento.getEmail());
		values.put(KEY_FLAG_DELETED, evento.getDeleted());
		values.put(KEY_FLAG_SAVED, flag_syn);
		values.put(KEY_CREATED_AT, evento.getCreated_at());
		values.put(KEY_UPDATED_AT, evento.getUpdated_at());
		values.put(KEY_AMBIENTE, evento.getAmbiente());
		values.put(KEY_ID_SUBTIPO_EVENTO, evento.getIdSubTipoEvento());
		
		// Inserting Row
		db.insert(TABLE_EVENTO, null, values);
		// Inserting Zones
		GestorZona gZona = new GestorZona(context);
		for (int i=0; i<evento.getZona().size(); i++)
			gZona.addZona(evento.getZona().get(i));
		
		db.close(); // Closing database connection
	}

	// Getting single tipoEvento
	public BeanEvento getEvento(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT  * FROM " + TABLE_EVENTO + " WHERE ID = " + id + ";";
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor != null)
			cursor.moveToFirst();
		BeanEvento evento = new BeanEvento();
		evento.setId(Integer.parseInt(cursor.getString(0)));
		evento.setNombre(cursor.getString(1));
		evento.setDescripcion(cursor.getString(2));
		evento.setTipoEvento(Integer.parseInt(cursor.getString(3)));
		evento.setFechaIni(cursor.getString(4));
		evento.setFechaFin(cursor.getString(5));
		evento.setEstado(cursor.getString(6));
		evento.setUrlImagen(cursor.getString(7));
		evento.setNombrePromotor(cursor.getString(8));
		evento.setApePaterno(cursor.getString(9));
		evento.setApeMaterno(cursor.getString(10));
		evento.setEmail(cursor.getString(11));
		evento.setAmbiente(cursor.getString(12));
		evento.setSaved(cursor.getString(13));	
		evento.setDeleted(cursor.getString(14));
		evento.setCreated_at(cursor.getString(15));
		evento.setUpdated_at(cursor.getString(16));	
		evento.setIdSubTipoEvento(Integer.parseInt(cursor.getString(17)));
		cursor.close();
		db.close();
		return evento;
	}
	
	// Se obtienen todos los eventos pendientes de aprobación estado = "1"
	public List<BeanEvento> getAllEventoPendienteAprobacion(int tipoEvento) {
		List<BeanEvento> eventos = new ArrayList<BeanEvento>();
		// Select All Query
		String estado = "1";
		String selectQuery = "SELECT  * FROM " + TABLE_EVENTO + " WHERE ESTADO = " + estado + " AND IDTIPOEVENTO = " + tipoEvento + " ;";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				BeanEvento evento = new BeanEvento();				
				evento.setId(Integer.parseInt(cursor.getString(0)));
				evento.setNombre(cursor.getString(1));
				evento.setDescripcion(cursor.getString(2));
				evento.setTipoEvento(Integer.parseInt(cursor.getString(3)));
				evento.setFechaIni(cursor.getString(4));
				evento.setFechaFin(cursor.getString(5));
				evento.setEstado(cursor.getString(6));
				evento.setUrlImagen(cursor.getString(7));
				evento.setNombrePromotor(cursor.getString(8));
				evento.setApePaterno(cursor.getString(9));
				evento.setApeMaterno(cursor.getString(10));
				evento.setEmail(cursor.getString(11));
				evento.setAmbiente(cursor.getString(12));
				evento.setSaved(cursor.getString(13));	
				evento.setDeleted(cursor.getString(14));
				evento.setCreated_at(cursor.getString(15));
				evento.setUpdated_at(cursor.getString(16));	
				evento.setIdSubTipoEvento(Integer.parseInt(cursor.getString(17)));
				// Adding tipoEvento to list
				Log.e("wa", evento.getFechaIni() + evento.getFechaFin() + evento.getNomPromotor() + evento.getEstado());
				eventos.add(evento);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return eventos;
	}
	
	// Se obtienen todos los eventos pre aprobados estado = "2"
	public List<BeanEvento> getAllEventoPreAprobado(int tipoEvento) {
		List<BeanEvento> eventos = new ArrayList<BeanEvento>();
		// Select All Query
		String estado = "2";
		String selectQuery = "SELECT  * FROM " + TABLE_EVENTO + " WHERE ESTADO = " + estado + " AND IDTIPOEVENTO = " + tipoEvento + " ;";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				BeanEvento evento = new BeanEvento();
				evento.setId(Integer.parseInt(cursor.getString(0)));
				evento.setNombre(cursor.getString(1));
				evento.setDescripcion(cursor.getString(2));
				evento.setTipoEvento(Integer.parseInt(cursor.getString(3)));
				evento.setFechaIni(cursor.getString(4));
				evento.setFechaFin(cursor.getString(5));
				evento.setEstado(cursor.getString(6));
				evento.setUrlImagen(cursor.getString(7));
				evento.setNombrePromotor(cursor.getString(8));
				evento.setApePaterno(cursor.getString(9));
				evento.setApeMaterno(cursor.getString(10));
				evento.setEmail(cursor.getString(11));
				evento.setAmbiente(cursor.getString(12));
				evento.setSaved(cursor.getString(13));	
				evento.setDeleted(cursor.getString(14));
				evento.setCreated_at(cursor.getString(15));
				evento.setUpdated_at(cursor.getString(16));		
				evento.setIdSubTipoEvento(Integer.parseInt(cursor.getString(17)));
				// Adding tipoEvento to list
				eventos.add(evento);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return eventos;
	}	
	
	public List<BeanEvento> getAll() {
		List<BeanEvento> eventos = new ArrayList<BeanEvento>();
		String selectQuery = "SELECT  * FROM " + TABLE_EVENTO + ";";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				BeanEvento evento = new BeanEvento();
				evento.setId(Integer.parseInt(cursor.getString(0)));
				evento.setNombre(cursor.getString(1));
				evento.setDescripcion(cursor.getString(2));
				evento.setTipoEvento(Integer.parseInt(cursor.getString(3)));
				evento.setFechaIni(cursor.getString(4));
				evento.setFechaFin(cursor.getString(5));
				evento.setEstado(cursor.getString(6));
				evento.setUrlImagen(cursor.getString(7));
				evento.setNombrePromotor(cursor.getString(8));
				evento.setApePaterno(cursor.getString(9));
				evento.setApeMaterno(cursor.getString(10));
				evento.setEmail(cursor.getString(11));
				evento.setAmbiente(cursor.getString(12));
				evento.setSaved(cursor.getString(13));	
				evento.setDeleted(cursor.getString(14));
				evento.setCreated_at(cursor.getString(15));
				evento.setUpdated_at(cursor.getString(16));	
				evento.setIdSubTipoEvento(Integer.parseInt(cursor.getString(17)));
				// Adding tipoEvento to list
				eventos.add(evento);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return eventos;
	}
	
	public List<BeanEvento> getAllNotSaved() {
		List<BeanEvento> eventos = new ArrayList<BeanEvento>();
		String selectQuery = "SELECT  * FROM " + TABLE_EVENTO + " WHERE " + KEY_FLAG_SAVED + " = '0';";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				BeanEvento evento = new BeanEvento();
				evento.setId(Integer.parseInt(cursor.getString(0)));
				evento.setNombre(cursor.getString(1));
				evento.setDescripcion(cursor.getString(2));
				evento.setTipoEvento(Integer.parseInt(cursor.getString(3)));
				evento.setFechaIni(cursor.getString(4));
				evento.setFechaFin(cursor.getString(5));
				evento.setEstado(cursor.getString(6));
				evento.setUrlImagen(cursor.getString(7));
				evento.setNombrePromotor(cursor.getString(8));
				evento.setApePaterno(cursor.getString(9));
				evento.setApeMaterno(cursor.getString(10));
				evento.setEmail(cursor.getString(11));
				evento.setAmbiente(cursor.getString(12));
				evento.setSaved(cursor.getString(13));	
				evento.setDeleted(cursor.getString(14));
				evento.setCreated_at(cursor.getString(15));
				evento.setUpdated_at(cursor.getString(16));
				evento.setIdSubTipoEvento(Integer.parseInt(cursor.getString(17)));
				// Adding tipoEvento to list
				eventos.add(evento);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return eventos;
	}

	// Updating single tipoEvento
	public void updateEvento(BeanEvento evento) {
		SQLiteDatabase db = this.getWritableDatabase();
		String consulta = "UPDATE " + TABLE_EVENTO 
						  + " SET " + KEY_ESTADO + " = '" 
						  + evento.getEstado()
						  + "', " + KEY_UPDATED_AT + " = '"
						  + evento.getUpdated_at()
						  + "', " + KEY_FLAG_DELETED + " = '"
						  + evento.getDeleted()
						  + "' WHERE " + KEY_ID + " = " + evento.getId() + ";";
		db.execSQL(consulta);
		db.close();
	}
	
	public void updateEvento2(BeanEvento evento) {
		
		SQLiteDatabase db = this.getWritableDatabase();
		String consulta = "UPDATE " + TABLE_EVENTO 
						  + " SET " + KEY_ESTADO + " = '" 
						  + evento.getEstado()  + "', "
						  + KEY_DESC + " = '" 
						  + evento.getDescripcion() + "', "
						  + KEY_ID_SUBTIPO_EVENTO + " = " 
						  + evento.getIdSubTipoEvento() + ", "
						  + KEY_NOMBRE + " = '" 
						  + evento.getNombre() + "', "
						  + KEY_FECHAINI  + " = '"
						  + evento.getFechaIni()  + "', "
						  + KEY_FECHAFIN + " = '" 
						  + evento.getFechaFin()  + "', "
						  + KEY_PROMOTOR + " = '"
						  + evento.getNombrePromotor() + "', "
						  + KEY_URL_IMAGE  + " = '"
						  + evento.getUrlImagen() + "', "
						  + KEY_APPATERNO  + " = '"
						  + evento.getApeMaterno()  + "', "
						  + KEY_APMATERNO  + " = '"
						  + evento.getApeMaterno() + "', "
						  + KEY_EMAIL  + " = '"
						  + evento.getEmail() + "', "
						  + KEY_AMBIENTE  + " = '"
						  + evento.getAmbiente() + "', "
						  + KEY_UPDATED_AT + " = '"
						  + evento.getUpdated_at()
						  + "' WHERE " + KEY_ID + " = " + evento.getId() + ";";
		db.execSQL(consulta);
		db.close();
	}
	
	// Updating single tipoEvento
	public void updateUrlEvento() {
		SQLiteDatabase db = this.getWritableDatabase();
		String consulta = "UPDATE " + TABLE_EVENTO 
						  + " SET " + KEY_URL_IMAGE + " = " 
						  + "'http://profile.ak.fbcdn.net/hprofile-ak-snc4/372136_1030047037_330333449_n.jpg'";
		db.execSQL(consulta);
		db.close();
	}

	// Deleting single tipoEvento
	public void deleteEvento(int idEvento) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_EVENTO, KEY_ID + " = ?",
				new String[] { String.valueOf(idEvento) });
		db.close();
	}
	
	public void deleteAll(){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("DELETE FROM " + TABLE_EVENTO);	
		db.close();
	}


	// Getting tipoEvento Count
	public int getTipoEventoCount() {
		String countQuery = "SELECT  * FROM " + TABLE_EVENTO;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		// return count
		db.close();
		return cursor.getCount();
	}
	
	public List<BeanEvento> getEventobyFiltro(String estado, String keyword) {
		List<BeanEvento> eventos = new ArrayList<BeanEvento>();
		// Select All Query
		//String estado = "2";
		String selectQuery = "SELECT  * FROM " + TABLE_EVENTO + " WHERE ESTADO = " + estado + " AND NOMBRE LIKE '%" + keyword + "%' ;";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				BeanEvento evento = new BeanEvento();
				evento.setId(Integer.parseInt(cursor.getString(0)));
				evento.setNombre(cursor.getString(1));
				evento.setDescripcion(cursor.getString(2));
				evento.setTipoEvento(Integer.parseInt(cursor.getString(3)));
				evento.setFechaIni(cursor.getString(4));
				evento.setFechaFin(cursor.getString(5));
				evento.setEstado(cursor.getString(6));
				evento.setUrlImagen(cursor.getString(7));
				evento.setNombrePromotor(cursor.getString(8));
				evento.setApePaterno(cursor.getString(9));
				evento.setApeMaterno(cursor.getString(10));
				evento.setEmail(cursor.getString(11));
				evento.setAmbiente(cursor.getString(12));
				evento.setSaved(cursor.getString(13));	
				evento.setDeleted(cursor.getString(14));
				evento.setCreated_at(cursor.getString(15));
				evento.setUpdated_at(cursor.getString(16));
				evento.setIdSubTipoEvento(Integer.parseInt(cursor.getString(17)));
				// Adding tipoEvento to list
				eventos.add(evento);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return eventos;
	}

	public void updateFlag(BeanEvento evento, String flag_syn) {
		// TODO Auto-generated method stub
		SQLiteDatabase db = this.getWritableDatabase();
		String consulta = "UPDATE " + TABLE_EVENTO 
						  + " SET " + KEY_FLAG_SAVED + " = " 
						  + flag_syn 
						  + " WHERE " + KEY_ID + " = " + evento.getId() + ";";
		db.execSQL(consulta);
		db.close();		
	}

	public void cleanData() {
		// TODO Auto-generated method stub
		String estado_rechazado="3";
		String estado_aprobado="4";
		SQLiteDatabase db = this.getWritableDatabase();
		String consulta = "DELETE FROM " + TABLE_EVENTO 
						  + " WHERE " + KEY_ESTADO + " = " + estado_rechazado 
						  + " OR " + KEY_ESTADO + " = " + estado_aprobado + ";";
		db.execSQL(consulta);
		db.close();		
	}

	public void syncData() {
		List<BeanEvento> listaEvento = getAllNotSaved();
		JsonEvento jEvento  =  new JsonEvento();
		for (int i=0; i<listaEvento.size(); i++){		
			try{
				int result = jEvento.postData(listaEvento.get(i).getId(),Integer.valueOf(listaEvento.get(i).getEstado()));
				if (result!=0) updateFlag(listaEvento.get(i),"1");
			} catch (IOException e) {
				e.printStackTrace();
			} catch (JSONException e) {
				e.printStackTrace();
			}	
		}
	}	
	
	public void setSaved(BeanPromocion promocion){
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("UPDATE " + TABLE_EVENTO + " SET " + KEY_FLAG_SAVED + " = 1 WHERE " + KEY_ID + " = " + promocion.getId()); 
		db.close();
	}

}
